--========================--
--section: create view--
--========================--
use online_news_editorial;
go


create view ViewAdvertisementDetails as
(
	SELECT	dbo.Advertisements.advId
			,dbo.Advertisements.advDateCreate
			,dbo.Advertisements.advDateModify
			,dbo.Advertisements.advDateExpire
			,dbo.Advertisements.advActive
			,dbo.Advertisements.advLink
			,dbo.Advertisements.advImageLink
			,dbo.Advertisements.advClickCount
			,dbo.AdvertisementContacts.advContId
			,dbo.AdvertisementContacts.advContName
			,dbo.AdvertisementContacts.advContCompany
			,dbo.AdvertisementContacts.advContPhone
			,dbo.AdvertisementBlocks.blkId
			,dbo.AdvertisementBlocks.blkName
			,dbo.AdvertisementBlocks.blkWidth
			,dbo.AdvertisementBlocks.blkHeight
			,dbo.AdvertisementBlocks.blkActive
			
	FROM	dbo.AdvertisementBlocks
			INNER JOIN dbo.Advertisements
				ON dbo.AdvertisementBlocks.blkId = dbo.Advertisements.advBlockId
			INNER JOIN dbo.AdvertisementContacts
				ON dbo.Advertisements.advContId = dbo.AdvertisementContacts.advContId
);
go



create view ViewEmployeeDetails as
(
	SELECT	dbo.JobAssignments.jaId
			,dbo.Divisions.divId
			,dbo.Divisions.divName
			,dbo.Employees.empId
			,dbo.Employees.empFirstName
			,dbo.Employees.empLastName
			,dbo.Employees.empGender
			,dbo.Employees.empDateModify
			,dbo.Employees.empDateJoin
			,dbo.Employees.empDateLeave
			,dbo.Jobs.jobId
			,dbo.Jobs.jobTitle
			,dbo.JobAssignments.jaDateModify
			,dbo.JobAssignments.jaDateStart
			,dbo.JobAssignments.jaDateFinish
			
	FROM	dbo.Divisions
			INNER JOIN dbo.JobAssignments
				ON dbo.Divisions.divId = dbo.JobAssignments.divId
			INNER JOIN dbo.Employees
				ON dbo.JobAssignments.empId = dbo.Employees.empId
			INNER JOIN dbo.Jobs
				ON dbo.JobAssignments.jobId = dbo.Jobs.jobId
);
go


-----Create view to declare job assignment based on division and category
--Create View vwJobDetails As
--(
--Select d.divId, d.divName, c.catParentId, c.catId, c.catName, ja.empId, j.jobId, j.jobTitle

--From dbo.Divisions d inner join dbo.Categories c
--on d.divId=c.belongToDivision
--inner join dbo.JobAssignments ja on d.divId=ja.divId
--inner join dbo.Jobs j on ja.jobId=j.jobId
--);
--go



CREATE VIEW ViewMembershipUserEmployee AS
(
	SELECT	dbo.Employees.empId
			, dbo.aspnet_Membership.UserId
			, dbo.aspnet_Users.UserName
			, dbo.Employees.empFirstName
			, dbo.Employees.empLastName
			, dbo.aspnet_Membership.Email
			, dbo.Employees.empGender
			, dbo.aspnet_Membership.IsApproved
			, dbo.aspnet_Roles.RoleName
						  
	FROM	dbo.aspnet_Membership
			INNER JOIN dbo.Employees
				ON dbo.aspnet_Membership.UserId = dbo.Employees.userId
			INNER JOIN dbo.aspnet_Users
				ON dbo.aspnet_Membership.UserId = dbo.aspnet_Users.UserId
				AND dbo.Employees.userId = dbo.aspnet_Users.UserId
			INNER JOIN dbo.aspnet_UsersInRoles
				ON dbo.aspnet_Users.UserId = dbo.aspnet_UsersInRoles.UserId
			INNER JOIN dbo.aspnet_Roles
				ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId
);
go